﻿using SdmxMl.Manager;
using SdmxMl.SmsImportCfg;
using SdmxMl.Structure;
using System;
using System.Data;
using System.IO;
using System.Xml;


namespace ChopPlugin
{
    // NOTHING TO DO WITH CHOP
    public class ShisHierarchy
    {
        #region Internal Members

        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        private CodeListType[] arrCodelistLevel;
        private HierarchicalCodelistType hcl;
        private HierarchyType hierarchy;

        private SmManager sm;
        #endregion

        public const string AGENCY_ID = "CRIME_TEST";

        private CodeListType cl630;
        private CodeListType cl630A;
        private CodeListType cl630B;
        public ShisHierarchy(string path)
        {
            sm = new SmManager();
            arrCodelistLevel = new CodeListType[3];
            cl630 = new CodeListType();
            cl630.AgencyId = AGENCY_ID;
            cl630.Id = "CL_630";
            cl630.Name.UpdateForLanguage("de", "Gesteze");
            arrCodelistLevel[0] = cl630;

            cl630A = new CodeListType();
            cl630A.AgencyId = AGENCY_ID;
            cl630A.Id = "CL_630A";
            cl630A.Name.UpdateForLanguage("de", "Gesetze Artikel");
            arrCodelistLevel[1] = cl630A;

            cl630B = new CodeListType();
            cl630B.AgencyId = AGENCY_ID;
            cl630B.Id = "CL_630B";
            cl630B.Name.UpdateForLanguage("de", "Ereignis Ripol");
            arrCodelistLevel[2] = cl630B;

            spreadsheetConcept = LoadSources(path);

            hcl = new HierarchicalCodelistType();
            hcl.AgencyId = AGENCY_ID;
            hcl.Id = "HCL_630";
            hcl.Name.UpdateForLanguage("de", "Gesteze Hierarchy");
            hcl.HierarchyList.Clear();
            sm.PutArtefact(SdmxArtefactType.HierarchicalCodelists, hcl);


            for (int i = 0; i < arrCodelistLevel.Length; i++)
            {
                 CodelistRefType clRef = new CodelistRefType();
                 clRef.AgencyId = AGENCY_ID;
                clRef.Alias = (i+1).ToString();
                clRef.Id = arrCodelistLevel[i].Id;
                clRef.Version = arrCodelistLevel[i].Version;                 
                hcl.CodelistRefList.Add(clRef);
                sm.PutArtefact(SdmxArtefactType.CodeLists, arrCodelistLevel[i]);
            }

            hierarchy = new HierarchyType(hcl.CodelistRefList);
            hierarchy.Id = "H_630";
            hierarchy.Name.UpdateDefaultText("Gesteze Hierarchy");
            hcl.HierarchyList.Add(hierarchy);

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                try
                {
                    CodeType code = new CodeType();
                    string c = wh.GetCellContent(row, 1);
                    if (c.Length == 0)
                        break;

                    string p = wh.GetCellContent(row, 2);
                    string level = wh.GetCellContent(row, 3);
                    string vf = wh.GetDateStr(row, 4);
                    string vt = wh.GetDateStr(row, 5);
                    string de = wh.GetCellContent(row, 7);
                    string fr = wh.GetCellContent(row, 8);
                    string it = wh.GetCellContent(row, 9);

                    code.Id = c;
                    code.Name.UpdateForLanguage("de", de);
                    code.Name.UpdateForLanguage("fr", fr);
                    code.Name.UpdateForLanguage("it", it);

                    switch (level)
                    {
                        case"1":
                            cl630.CodeList.Add(code);

                        break;

                        case "2":
                            cl630A.CodeList.Add(code);
                        break;

                        case "3":
                            cl630B.CodeList.Add(code);
                        break;
                    }

                    SetHierarchyRef(code, int.Parse(level), p, vf, vt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            XmlDocument xDoc = sm.GetAsDocument();
            xDoc.Save("c:\\doc\\crime\\cl_630h.xml");
            
        }

        private void SetHierarchyRef(CodeType c, int level, string parentCode, string vf, string vt)
        {
            CodeRefTypeList crefList = hierarchy.CodeRefList;
            if (level == 2)
            {
                // get parent
                foreach (CodeRefType cRef in  hierarchy.CodeRefList)
                    if (cRef.CodeID == parentCode)
                    {
                        crefList = cRef.CodeRefList;
                        break;

                    }
            }
            else if (level == 3)
            {
                // get parent
                foreach (CodeRefType cRef in hierarchy.CodeRefList)
                {
                    foreach (CodeRefType cRefchild in cRef.CodeRefList)
                    {
                        if (cRefchild.CodeID == parentCode)
                        {
                            crefList = cRefchild.CodeRefList;
                            break;
                        }
                    }
                    if (crefList != hierarchy.CodeRefList)
                        break;
                }
            }


            CodeRefType cref = new CodeRefType(c.Id, level.ToString());
            cref.ValidFrom = vf;
            cref.ValidTo = vt;
            crefList.Add(cref);

        }


        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }
   }
}
